﻿using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace syc.Sale
{
    public partial class 辅料采购计划管理 : System.Web.UI.Page
    {
        private string RowColor = "aaff66";
        private string sql =
@"select t1.ID, case when t1.MID is null OR t1.MID=0 then t1.ID else t1.MID end as MID
,cast(t1.年度 as varchar)+'-'+ (case when t1.月份<10 then '0'+cast(t1.月份 as varchar) else cast(t1.月份 as varchar) end) as 计划
,t1.辅料ID,isnull(t2.品名,'') as 品名,isnull(t2.规格,'') as 规格,isnull(t2.单位,'') as 单位
,t1.填报数量,t1.计划购进量,t1.单价,t1.单价*t1.计划购进量 as 金额,t1.填报日期
,t3.名称 as 填报单位,isnull(t1.用途,'') as 用途,isnull(t1.备注,'') as 备注,是否到货,到货日期,t1.供货单位
from 辅料采购计划0 t1
left outer join 物料品种 t2 on t1.辅料ID=t2.ID
left outer join 设备编码 t3 on t1.填报单位=t3.编码 and t3.资产=0
where t1.状态=0 ";
        int lastmid = 0;
        protected void Page_Load(object sender, EventArgs e)
        {
            this.KuCun.Text = "";
            if (!this.IsPostBack)
            {
                this.SqlDataSource1.SelectCommand = sql + " order by 填报日期 desc";
                this.SqlText.Text = this.SqlDataSource1.SelectCommand;
            }
            else
            {
                this.SqlDataSource1.SelectCommand = this.SqlText.Text;
            }
            this.Panel_Msg.Visible = false;
        }

        protected void GridView1_DataBound(object sender, EventArgs e)
        {

            this.TextBox_Pager.Text = (this.GridView1.PageIndex + 1).ToString();
            this.Label_Pages.Text = " 共" + this.GridView1.PageCount.ToString() + "页 ";

            this.Button_Pager_First.CssClass = "pageturn-first-b";
            this.Button_Pager_First.Enabled = true;
            this.Button_Pager_Prev.CssClass = "pageturn-prev-b";
            this.Button_Pager_Prev.Enabled = true;
            this.Button_Pager_Next.CssClass = "pageturn-next-b";
            this.Button_Pager_Next.Enabled = true;
            this.Button_Pager_Last.CssClass = "pageturn-last-b";
            this.Button_Pager_Last.Enabled = true;
            if (this.GridView1.PageIndex == 0)
            {
                this.Button_Pager_Prev.CssClass = "pageturn-prev-g";
                this.Button_Pager_Prev.Enabled = false;
            }
            if (this.GridView1.PageIndex < 2)
            {
                this.Button_Pager_First.CssClass = "pageturn-first-g";
                this.Button_Pager_First.Enabled = false;
            }
            if (this.GridView1.PageIndex == this.GridView1.PageCount - 1 || this.GridView1.PageCount == 0)
            {
                this.Button_Pager_Next.CssClass = "pageturn-next-g";
                this.Button_Pager_Next.Enabled = false;

            }
            if (this.GridView1.PageIndex > this.GridView1.PageCount - 3)
            {
                this.Button_Pager_Last.CssClass = "pageturn-last-g";
                this.Button_Pager_Last.Enabled = false;
            }

        }
        protected void GridView1_PageIndexChanged(object sender, EventArgs e)
        {
            this.GridView1_DataBound(null, null);
        }
        protected void Pager_Click(object sender, EventArgs e)
        {
            if (sender.GetType().ToString() == "System.Web.UI.WebControls.Button")
            {
                switch (((Button)sender).CommandName)
                {
                    case "--":
                        this.GridView1.PageIndex = 0;
                        break;
                    case "-":
                        if (this.GridView1.PageIndex > 0) this.GridView1.PageIndex--;
                        break;
                    case "+":
                        if (this.GridView1.PageIndex < this.GridView1.PageCount - 1) this.GridView1.PageIndex++;
                        break;
                    case "++":
                        this.GridView1.PageIndex = this.GridView1.PageCount - 1;
                        break;
                }
            }
            else
            {

                int p;
                if (int.TryParse(TextBox_Pager.Text, out p))
                {
                    if (p >= 1 && p <= this.GridView1.PageCount)
                    {
                        this.GridView1.PageIndex = p - 1;
                    }
                }
                TextBox_Pager.Text = (this.GridView1.PageIndex + 1).ToString();
            }

        }
        protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
        {
            Label_Total.Text = "共查询到 " + e.AffectedRows + " 项记录";
        }

        private string GetState()
        {
            string state = "";
            state = " and t1.填报单位 like '" + this.TianBao.Text + "%'";

            if (this.NianDu.Text != "0")
            {
                state += " and t1.年度=" + this.NianDu.Text;
            }
            if (this.YueFen.Text != "0")
            {
                state += " and t1.月份=" + this.YueFen.Text;
            }

            DateTime d;
            if (DateTime.TryParse(this.Date1.Text, out d))
            {
                state += " and t1.填报日期>='" + d.ToShortDateString() + "'";
            }
            if (DateTime.TryParse(this.Date2.Text, out d))
            {
                state += " and t1.填报日期<='" + d.ToShortDateString() + "'";
            }
            return state;
        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {
            string part1 =
            @"<html xmlns:o='urn:schemas-microsoft-com:office:office'
xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns='http://www.w3.org/TR/REC-html40'>
<head>
<meta http-equiv=Content-Type content='text/html; charset=utf-8'>
<style>
<!--table
@page
{mso-header-data:;
mso-footer-data:;
margin:.39in .12in .39in .12in;
mso-header-margin:.51in;
mso-footer-margin:.51in;
mso-page-orientation:landscape;
mso-horizontal-page-align:center;
}
br{mso-data-placement:same-cell;}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>采购计划</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:FitToPage/>
     <x:FitToPage/>
     <x:Print>
      <x:ValidPrinterInfo/>
      <x:Scale>100</x:Scale>
      <x:PaperSizeIndex>9</x:PaperSizeIndex>
      <x:HorizontalResolution>200</x:HorizontalResolution>
      <x:VerticalResolution>200</x:VerticalResolution>
     </x:Print>
     <x:Selected/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>0</x:ActiveRow>
       <x:ActiveCol>0</x:ActiveCol>
      </x:Pane>
     </x:Panes>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body>";

            string part2 =
@"<style>
td{border:.5pt solid #000000;font-size:10pt;text-align:center;mso-number-format:\@;}
td.nb{border:none;}
</style>
<table border=0 cellpadding=0 style=border-collapse:collapse>
<tr>
<td class=nb colspan=8 style=font-size:14pt>辅料采购制作计划表</td>
</tr>
<tr>
<td class=nb colspan=2 style=text-align:left>&nbsp;填报单位：供应处</td>
<td class=nb colspan=6 style=text-align:right>&nbsp;</td>
</tr>
<tr style=height:18pt>
<td style=width:100pt;>品名</td>
<td style=width:100pt;>规格型号</td>
<td style=width:50pt;>单 位</td>
<td style=width:70pt;>数 量</td>
<td style=width:80pt;>单价(元)</td>
<td style=width:80pt;>金额(元)</td>
<td style=width:80pt;>使用单位</td>
<td style=width:150pt;>备 注</td>
</tr>";


            Response.Charset = "utf-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("采购计划") + ".xls\"");

            Response.Write(part1);
            Response.Write(part2);

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = sql + this.GetState() + " order by 辅料ID asc";

            Cmd.CommandType = CommandType.Text;
            SqlDataReader Reader = Cmd.ExecuteReader();
            int lbid = 0;
            string zhuji = "";
            double shuliang = 0;
            string shiyong = "";
            string beizhu = "";
            string mingxi = "";

            string mingcheng = "";
            string guige = "";
            string danwei = "";
            decimal danjia = 0;
            int n = 3;
            if (Reader.Read())
            {
                while (true)
                {
                    if (lbid != (int)Reader["辅料ID"])
                    {
                        if (lbid != 0)
                        {
                            n++;
                            Response.Write("<tr><td style=text-align:center>" + mingcheng + "</td>");
                            Response.Write("<td style=text-align:center>" + guige + "</td>");
                            Response.Write("<td>" + danwei + "</td>");
                            Response.Write("<td style=text-align:right>" + shuliang + "</td>");
                            Response.Write("<td style=text-align:right>" + danjia.ToString("0.00") + "</td>");
                            Response.Write("<td style=text-align:right;mso-number-format:Standard x:num>" + (double.Parse(danjia.ToString()) * shuliang) + "</td>");
                            if (mingxi.IndexOf("<br>") > -1)
                            {
                                Response.Write("<td style=text-align:center>" + mingxi + "</td>");
                            }
                            else
                            {
                                Response.Write("<td style=text-align:center>" + shiyong + "</td>");
                            }
                            Response.Write("<td style=text-align:center>" + beizhu + "</td></tr>");
                        }

                        mingcheng = (string)Reader["品名"];
                        guige = (string)Reader["规格"];
                        danwei = (string)Reader["单位"];
                        danjia = (decimal)Reader["单价"];

                        zhuji = (string)Reader["用途"];
                        shuliang = (double)Reader["计划购进量"];
                        shiyong = (string)Reader["填报单位"];
                        mingxi = (string)Reader["填报单位"] + (double)Reader["计划购进量"] + (string)Reader["单位"];
                        beizhu = (string)Reader["备注"];
                        lbid = (int)Reader["辅料ID"];
                    }
                    else
                    {
                        zhuji = zhuji + "<br>" + (string)Reader["用途"];
                        shuliang = shuliang + (double)Reader["计划购进量"];
                        mingxi = mingxi + "<br>" + (string)Reader["填报单位"] + (double)Reader["计划购进量"] + (string)Reader["单位"];
                        beizhu = beizhu + "<br>" + (string)Reader["备注"];
                    }
                    if (!Reader.Read())
                    {
                        n++;
                        Response.Write("<tr><td style=text-align:center>" + mingcheng + "</td>");
                        Response.Write("<td style=text-align:center>" + guige + "</td>");
                        Response.Write("<td>" + danwei + "</td>");
                        Response.Write("<td style=text-align:right>" + shuliang + "</td>");
                        Response.Write("<td style=text-align:right>" + danjia.ToString("0.00") + "</td>");
                        Response.Write("<td style=text-align:right;mso-number-format:Standard x:num>" + (double.Parse(danjia.ToString()) * shuliang) + "</td>");
                        if (mingxi.IndexOf("<br>") > -1)
                        {
                            Response.Write("<td style=text-align:center>" + mingxi + "</td>");
                        }
                        else
                        {
                            Response.Write("<td style=text-align:center>" + shiyong + "</td>");
                        }
                        Response.Write("<td style=text-align:left>" + beizhu + "</td></tr>");
                        break;
                    }
                }
            }
            if (n > 3)
            {
                Response.Write("<tr><td class=nb ></td><td class=nb ></td><td class=nb ></td><td class=nb ></td>"
                    + "<td class=nb style=text-align:right>合计：</td><td class=nb style='text-align:right;mso-number-format:Standard' x:num x:fmla='=SUM(F4:F"
                    + n + ")'>0</td><td class=nb></td><td class=nb></td></tr>");
            }
            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();

            Response.Write("</table></body></html>");
            Response.End();


            //SqlConnection Conn = new SqlConnection();
            //Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            //Conn.Open();
            //SqlCommand Cmd = new SqlCommand();
            //Cmd.Connection = Conn;
            //Cmd.CommandText = sql + this.GetState();
            //Cmd.CommandType = CommandType.Text;
            //SqlDataReader Reader = Cmd.ExecuteReader();   

            //string mingcheng = "";
            //string guige = "";
            //string danwei = "";
            //double shuliang = 0;
            //decimal danjia = 0;
            //double  jine = 0;
            //string shiyong = "";
            //int i=0;
            //double heji = 0;
            //while (Reader.Read())
            //{
            //    ++i;
            //    mingcheng = (string)Reader["品名"];
            //    guige = (string)Reader["规格"];
            //    danwei = (string)Reader["单位"];
            //    danjia = (decimal)Reader["单价"];


            //    shuliang = (double)Reader["计划购进量"];
            //    shiyong = (string)Reader["填报单位"];
            //    shiyong = (string)Reader["填报单位"];
            //    jine = (double)danjia * shuliang;
            //    heji = heji + jine;
            //            Response.Write("<tr height=28 style='mso-height-source:userset;height:21.0pt'><td height=28 class=xl26 style='height:21.0pt;border-top:none'>"+i+"</td>");
            //            Response.Write("  <td class=xl26 style='border-top:none;border-left:none'>" + mingcheng + "</td>");
            //            Response.Write("<td class=xl26 style='border-left:none'>" + guige + "</td>");
            //            Response.Write("<td class=xl26 style='border-left:none'>" + danwei + "</td>");
            //            Response.Write("<td class=xl26 style='border-left:none'>" + shuliang + "</td>");
            //            Response.Write("<td class=xl26 style='border-left:none'>" + danjia + "</td>");
            //            Response.Write("<td class=xl26 style='border-left:none'>"+jine+"</td>");

            //            Response.Write("<td class=xl26 style='border-left:none'>" + shiyong + "</td>");
            //            Response.Write("<td></td></tr>");






            //    }
            //Response.Write(" <tr height=19 style='height:14.25pt'>");
            //Response.Write("<td height=19 class=xl29 style='height:14.25pt;border-top:none'>　</td>  <td class=xl29 style='border-top:none;border-left:none'>　</td>  <td class=xl29 style='border-top:none;border-left:none'>　</td>  <td class=xl29 style='border-top:none;border-left:none'>　</td>  <td class=xl29 style='border-top:none;border-left:none'>　</td>");
            //Response.Write("<td class=xl30 style='border-top:none;border-left:none'>合计:</td>");
            //Response.Write("<td class=xl29 style='border-top:none;border-left:none'>" + heji + "</td>");
            //Response.Write("<td class=xl29 style='border-top:none;border-left:none'>　</td>");
            //Response.Write("<td></td> </tr>");
            //Reader.Close();
            //Conn.Close();
            //Reader.Dispose();
            //Cmd.Dispose();
            //Conn.Dispose();
            string part3 = @"<![if supportMisalignedColumns]>
 <tr height=0 style='display:none'>
  <td width=133 style='width:100pt'></td>
  <td width=240 style='width:180pt'></td>
  <td width=53 style='width:40pt'></td>
  <td width=67 style='width:50pt'></td>
  <td width=107 style='width:80pt'></td>
  <td width=107 style='width:80pt'></td>
  <td width=107 style='width:80pt'></td>
  <td width=133 style='width:100pt'></td>
 </tr>
 <![endif]>
</table>

</body>

</html>
";
            Response.Write(part3);

            Response.End();
        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {
            this.GridView1.EditIndex = -1;
            this.GridView1.SelectedIndex = -1;
            this.Button_Delete.Enabled = false;
            this.Button_Edit.Enabled = false;
            this.SqlText.Text = sql + this.GetState() + " order by 填报日期 desc";
            this.SqlDataSource1.SelectCommand = "";
            this.SqlDataSource1.SelectCommand = this.SqlText.Text;
        }

        protected void Button_Clear_Click(object sender, EventArgs e)
        {
            this.TianBao.Text = "35";
            this.NianDu.SelectedIndex = 0;
            this.YueFen.SelectedIndex = 0;
            this.Date1.Text = "";
            this.Date2.Text = "";

        }

        protected void Button_Delete_Click(object sender, EventArgs e)
        {
            this.GridView1.SelectedIndex = -1;
            this.Button_Delete.Enabled = false;
            this.Button_Edit.Enabled = false;
            this.SqlDataSource1.DeleteCommand = "delete from 辅料采购计划0 where ID=" + this.SelectedID.Text;
            this.SqlDataSource1.Delete();
        }

        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            this.GridView1.SelectedIndex = e.NewEditIndex;
            this.SelectedID.Text = this.GridView1.SelectedDataKey[0].ToString();
            //获得品名
            string 品名 = ((Label)this.GridView1.Rows[this.GridView1.SelectedIndex].FindControl("Label_ph")).Text;
            string 单位 = ((Label)this.GridView1.Rows[this.GridView1.SelectedIndex].FindControl("Label_dw")).Text;
            string 品名ID = ((Label)this.GridView1.Rows[this.GridView1.SelectedIndex].FindControl("Label_bm")).Text;
            this.KuCun.Text = "现有" + 品名 + "库存:" + GetKuCun(品名ID) + 单位;
            this.BillID.Text = this.GridView1.SelectedDataKey[1].ToString();
            this.Button_Delete.Enabled = true;
            this.Button_Edit.Enabled = true;
            this.Button_Edit.OnClientClick = "parent.swin_load('/Sale/辅料采购计划修改.aspx?id=" + this.BillID.Text + "');return false;";
        }
        protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.DataItem != null)
            {
                int mid = (int)((DataRowView)e.Row.DataItem).Row.ItemArray[1];
                if (mid != lastmid)
                {
                    lastmid = mid;
                    if (this.RowColor == "#aaff66")
                    {
                        this.RowColor = "#aa66ff";
                    }
                    else
                    {
                        this.RowColor = "#aaff66";
                    }
                }
                e.Row.Cells[0].BackColor = System.Drawing.Color.FromName(this.RowColor);
            }
        }
        //原料库存
        protected double GetKuCun(string 品名ID)
        {
            double 库存 = 0;
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            string sql = @"
            declare @kucun float
            select @kucun=isnull(sum(数量),0) from 材料入出库 where 材料ID=(select ID from [物料品种] where ID='@品名ID') and 日期<='@日期' 
            select @kucun";
            sql = sql.Replace("@品名ID", 品名ID);
            sql = sql.Replace("@日期", DateTime.Today.ToString());
            Cmd.CommandText = sql;
            SqlDataReader Reader = Cmd.ExecuteReader();
            //实有库存
            if (Reader.Read())
            {
                库存 = Reader.GetDouble(0);
            }
            Reader.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
            return 库存;
        }

        protected int Check(string check, string ID)
        {

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            try
            {
                string op = (string)Session["OperatorName"];
                if (op == null)
                {
                    op = "";
                }
                Cmd.CommandText = "大宗辅料是否到货";
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.Parameters.Add(new SqlParameter("@是否到货", SqlDbType.Bit, 20, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, check));
                Cmd.Parameters.Add(new SqlParameter("@计划ID", SqlDbType.Int, 10, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, int.Parse(ID)));
                Cmd.Parameters.Add(new SqlParameter("@返回值", SqlDbType.Int, 4, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Proposed, null));
                Cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                this.Message.Text = err.Message;
            }
            Cmd.Dispose();
            Conn.Close();
            Conn.Dispose();
            return (int)Cmd.Parameters["@返回值"].Value;
        }
        //合格判定
        protected void CheckBox1_CheckedChanged(object sender, EventArgs e)
        {
            var ch = "";
            if (((CheckBox)sender).Checked)
            {
                ch = "true";
            }
            else
            {
                ch = "false";
            }
            var id = ((CheckBox)sender).ToolTip;
            var check = Check(ch, id);
            if (check == 1)
            {
                this.Message.Text = "·判定成功！";
            }
            else
            {
                this.Message.Text = "·判定失败！";
            }
            this.GridView1.SelectedIndex = -1;
            GridView1.DataBind();
        }


        protected void GongHuo_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.GongHuo1.Text = ((DropDownList)sender).Text;
        }
    }
}
